# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: http://doc.scrapy.org/en/latest/topics/item-pipeline.html
import codecs
import json
import time

from scrapy import signals
from scrapy.exceptions import DropItem
from scrapy.xlib.pydispatch import dispatcher
import MySQLdb

# from tutorial.function import common
# from tutorial.function import sql_helper

class ZhiLianPipeline(object):
    def __init__(self):
        self.conn = MySQLdb.connect(host="127.0.0.1", user='root', passwd='root', db='zhilian', port=3306,
                               charset='utf8')

    def process_item(self, item, spider):
        if item['zwmc']:
            cursor = self.conn.cursor()
            #公司分类
            cursor.execute("select * from zl_gsmc where name = %s limit 1", [item['gsmc']])
            result = cursor.fetchall()
            if (len(result) == 0):
                insert_sql = "Insert into zl_gsmc(name) VALUE (%s)"
                insert_parm = [item['gsmc']]
                cursor.execute(insert_sql, insert_parm)
                cursor.execute("select * from zl_gsmc where name = %s limit 1", [item['gsmc']])
                result = cursor.fetchone()
                gsmc_id = result[0]
            else:
                gsmc_id = result[0][0]
        
                
            #确定工作地点的分类
            cursor.execute("select * from zl_gzdd where name = %s limit 1", [item['gzdd']])
            result = cursor.fetchall()
            if (len(result) == 0):
                insert_sql = "Insert into zl_gzdd(name) VALUE (%s)"
                insert_parm = [item['gzdd']]
                cursor.execute(insert_sql, insert_parm)
                cursor.execute("select * from zl_gzdd where name = %s limit 1", [item['gzdd']])
                result = cursor.fetchone()
                gzdd_id = result[0]
            else:
                gzdd_id = result[0][0]

            #确定公司性质的分类
            cursor.execute("select * from zl_gsxz where name = %s limit 1", [item['gsxz']])
            result = cursor.fetchall()
            if (len(result) == 0):
                insert_sql = "Insert into zl_gsxz(name) VALUE (%s)"
                insert_parm = [item['gsxz']]
                cursor.execute(insert_sql, insert_parm)
                cursor.execute("select * from zl_gsxz where name = %s limit 1", [item['gsxz']])
                result = cursor.fetchone()
                gsxz_id = result[0]
            else:
                gsxz_id = result[0][0]

            #确定公司规模的分类
            cursor.execute("select * from zl_gsgm where name = %s limit 1", [item['gsgm']])
            result = cursor.fetchall()
            if (len(result) == 0):
                insert_sql = "Insert into zl_gsgm(name) VALUE (%s)"
                insert_parm = [item['gsgm']]
                cursor.execute(insert_sql, insert_parm)
                cursor.execute("select * from zl_gsgm where name = %s limit 1", [item['gsgm']])
                result = cursor.fetchone()
                gsgm_id = result[0]
            else:
                gsgm_id = result[0][0]
                
            #确定工作经验的分类
            cursor.execute("select * from zl_gzjy where name = %s limit 1", [item['gzjy']])
            result = cursor.fetchall()
            if (len(result) == 0):
                insert_sql = "Insert into zl_gzjy(name) VALUE (%s)"
                insert_parm = [item['gzjy']]
                cursor.execute(insert_sql, insert_parm)
                cursor.execute("select * from zl_gzjy where name = %s limit 1", [item['gzjy']])
                result = cursor.fetchone()
                gzjy_id = result[0]
            else:
                gzjy_id = result[0][0]
                
            #确定学历的分类
            cursor.execute("select * from zl_xl where name = %s limit 1", [item['xl']])
            result = cursor.fetchall()
            if (len(result) == 0):
                insert_sql = "Insert into zl_xl(name) VALUE (%s)"
                insert_parm = [item['xl']]
                cursor.execute(insert_sql, insert_parm)
                cursor.execute("select * from zl_xl where name = %s limit 1", [item['xl']])
                result = cursor.fetchone()
                xl_id = result[0]
            else:
                xl_id = result[0][0]
            #职位名称
            cursor.execute("select * from zl_zwmc where name = %s limit 1", [item['zwmc']])
            result = cursor.fetchall()
            if (len(result) == 0):
                insert_sql = "Insert into zl_zwmc(name) VALUE (%s)"
                insert_parm = [item['zwmc']]
                cursor.execute(insert_sql, insert_parm)
                cursor.execute("select * from zl_zwmc where name = %s limit 1", [item['zwmc']])
                result = cursor.fetchall()
                zwmc_id = result[0][0]
            else:
                zwmc_id = result[0][0]

            sql2 = "Insert into zl_list(zwmc,href,gsmc,zwyx,gzdd,gxsj,gsxz,gsgm,gzjy,xl,remark) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}')"
            cursor.execute(sql2.format(zwmc_id,item['href'],gsmc_id,item['zwyx'],gzdd_id,item['gxsj'],gsxz_id,gsgm_id,gzjy_id,xl_id,item['remark']) )
            self.conn.commit()
        else:
            raise DropItem("Missing price in %s" % item)
        

# class CsdnblogPipeline(object):
#     def __init__(self):
#         self.file = codecs.open('CSDNBlog_data.json', mode='wb', encoding='utf-8')
#         # signals.dispatcher.connect(self.spider_opened, signals.spider_opened)
#         dispatcher.connect(self.spider_closed, signals.spider_closed)
#
#     def process_item(self, item, spider):
#         if 'title' in item:
#             line = json.dumps(dict(item)) + "\n"
#         self.file.write(line.decode('unicode_escape'))
#         if (item['FunctionStyle'] == "InsertUser"):
#             sql_helper.insert_user(item['UserName'], item['Fellow'], item['Fans'], item['Score'], item['UserInfoUrl'],
#                                    item['Source'], item['UserWordUrl'], item['NickName'], item['Description'],
#                                    item['Skill'], item['Email'], item['Phone'], item['QQ'], item['WeiXin'],
#                                    item['Field'], item['Info'])
#         elif (item['FuctionStyle'] == "InsertWord"):
#             sql_helper.insert_query(item['TableName'], item['Source'], item['Title'], item['Category'],
#                                     item['Description'], item['URL'], item['Lable'], item['ReplyCount'],
#                                     item['Browses'], item['Author'])
#         elif (item['FunctionStyle'] == "UpdateUserInfo"):
#             sql_helper.update_user_Info(item['UserName'], item['Source'], item['Colunm'], item['Data'])
#         elif (item['FunctionStyle']=="City"):
#             sql_helper.insert_city(item['title'])
#         return item
#     def spider_closed(self, spider, reason):
#         ISOTIMEFORMAT = '%Y-%m-%d %X'
#         fout = open('pipelines.txt', 'w')
#         try:
#             self.file.close()
#             word = ""
#             f = open(spider.name+".txt", "r")
#             lines = f.readlines()
#             for line in lines:
#                 word += line + "<br>"
#             f.close()
#             #f2 = open("except.txt", "r")
#             #lines = f2.readlines()
#             #for line in lines:
#             #    word += line + "<br>"
#             #f2.close()
#             if(len(word)!=0):
#                 common.send_mail(spider.name+"_"+reason + ",Error", word)
#             else:
#                 common.send_mail(reason, spider.name + "_" + reason)
#         except Exception as e:
#             string = "Error: %s,Time:%s\n" % (e, time.strftime(ISOTIMEFORMAT, time.localtime()))
#             fout.write(string)
